--EXEC SP_GET_ALL_DIEBANK_RUNNING_LOTS 0,0,0, 'FALSE', 'FALSE'
ALTER PROCEDURE SP_GET_ALL_DIEBANK_RUNNING_LOTS
( 
 @PROCESSSTEP_ID INT,
 @LOCATION_ID INT,
 --@VENDOR_ID INT  --USED EXTERNAL USER  
 @SEARCH_LOT_ID INT,
 @IS_ASSY_RELEASE VARCHAR(10),
 @IS_SHOW_RELEASE_LOT VARCHAR(10)
)

AS
BEGIN
	CREATE TABLE #RESULTS  ( LOT_ID INT, PROCESSSTEP_ID INT, PART_NUMBER VARCHAR(50),PS_TYPE VARCHAR(50), LOT_NUMBER VARCHAR(50),FAB_LOT_NUMBER VARCHAR(50), VENDOR_NAME VARCHAR(50), QTY_IN INT, WAFER_IDS VARCHAR(100), BIN_QTY_STRING VARCHAR(200), NEXT_PROCESS VARCHAR(50), NEXT_VENDOR VARCHAR(50), RELEASE_FLAG BIT )
		
	DECLARE @QUERYSTRING	VARCHAR(2000)
	DECLARE @UNION_QUERYSTRING	VARCHAR(20)
	DECLARE @ASSY_REL_QUERYSTRING	VARCHAR(2000)

	SET @QUERYSTRING = N'
 	INSERT INTO #RESULTS 
 	SELECT DISTINCT LT.LOT_ID, PS.PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, LOT_NUMBER, FAB_LOT_NUMBER, L_VENDOR_NAME ,SUM(ISNULL(BIN_1_BALANCE_QTY, 0)) AS QTY_IN , NULL, NULL, ''Assembly'', NULL,0
	FROM LOT_TABLE LT, PROCESS_STEP PS, LOCATION L, PRODUCT P, SORT_BIN_RELEASE_TABLE SBRT 
	WHERE 
	LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = SBRT.LOT_ID AND
	LT.LOCATION_ID = L.LOCATION_ID AND ISNULL(BIN_1_BALANCE_QTY, 0) > 0 AND LT.Lot_Current_Status = ''Running'''
	
 	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0  )
 	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND PS.PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@PROCESSSTEP_ID)
 	END
	
	IF ( @LOCATION_ID IS NOT NULL AND @LOCATION_ID > 0   )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOCATION_ID = ' + CONVERT(VARCHAR,@LOCATION_ID)
 	END

	IF ( @SEARCH_LOT_ID IS NOT NULL AND @SEARCH_LOT_ID > 0   )
	BEGIN
 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOT_ID = ' + CONVERT(VARCHAR,@SEARCH_LOT_ID)
 	END
	
	SET @QUERYSTRING = @QUERYSTRING + ' GROUP BY LT.LOT_ID,PS.PROCESSSTEP_ID,PART_NUMBER,PS_TYPE,LOT_NUMBER, FAB_LOT_NUMBER, L_VENDOR_NAME '
	
	SET @UNION_QUERYSTRING = N' UNION '

	--THIS QUERY IS FOR ASEMEBLY RELEASE LOTS WHICH NOT SHIPPED TO VENDOR
	SET @ASSY_REL_QUERYSTRING = N' SELECT DISTINCT LT.LOT_ID, PS.PROCESSSTEP_ID, PART_NUMBER, PS_TYPE, SHIPPED_LOT_NUMBER, FAB_LOT_NUMBER, L_VENDOR_NAME , SUM(Qty_Pulled), NULL, NULL, ''Assembly'', NULL, 1 
				FROM ASSM_RELEASE_TABLE ART , ASSM_RELEASE_LOT_TABLE ARLT,LOT_TABLE LT, PROCESS_STEP PS, LOCATION L, PRODUCT P 
				WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> ''CANCELLED'' AND ARLT.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND
				LT.LOCATION_ID = L.LOCATION_ID AND Lot_Transferred = 1 AND Shipped_Lot_Number IS NOT NULL '	
	
	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0'  )
 	BEGIN
 		SET @ASSY_REL_QUERYSTRING = @ASSY_REL_QUERYSTRING +' AND PS.PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@PROCESSSTEP_ID)
 	END
	
	IF ( @LOCATION_ID IS NOT NULL AND @LOCATION_ID <> '0'  )
	BEGIN
 		SET @ASSY_REL_QUERYSTRING = @ASSY_REL_QUERYSTRING +' AND LT.LOCATION_ID = ' + CONVERT(VARCHAR,@LOCATION_ID)
 	END

	IF ( @SEARCH_LOT_ID IS NOT NULL AND @SEARCH_LOT_ID <> '0'  )
	BEGIN
 		SET @ASSY_REL_QUERYSTRING = @ASSY_REL_QUERYSTRING +' AND LT.LOT_ID = ' + CONVERT(VARCHAR,@SEARCH_LOT_ID)
 	END
	
	SET @ASSY_REL_QUERYSTRING = @ASSY_REL_QUERYSTRING + ' GROUP BY LT.LOT_ID,PS.PROCESSSTEP_ID,PART_NUMBER,PS_TYPE,SHIPPED_LOT_NUMBER, FAB_LOT_NUMBER, L_VENDOR_NAME '
	
	--PRINT '@QUERYSTRING :- '+@QUERYSTRING
	--PRINT '@ASSY_REL_QUERYSTRING :- '+@ASSY_REL_QUERYSTRING
	--PRINT @QUERYSTRING + @UNION_QUERYSTRING + @ASSY_REL_QUERYSTRING

	IF @IS_ASSY_RELEASE = 'TRUE'
	BEGIN
		 EXEC ('INSERT INTO #RESULTS' + @ASSY_REL_QUERYSTRING)
	END
	ELSE
	BEGIN
		 EXEC (@QUERYSTRING + @UNION_QUERYSTRING + @ASSY_REL_QUERYSTRING)
	END
		
	DECLARE @LOT_ID			INT
	DECLARE @WAFER_IDS	VARCHAR(100)
	DECLARE @WAFERS_TABLE	TABLE ( WAFER_ID INT, QTY_IN INT )
	DECLARE @QTY_AS_STRING	VARCHAR(200)
	DECLARE @DB_PROCESSSTEP_ID INT 
	DECLARE @ASSY_PROCESSSTEP_ID INT 
	DECLARE @LOT_SIZE INT 
	DECLARE @WIPCURSOR CURSOR
	DECLARE @NEXT_VENDOR_NAME VARCHAR(50)
	DECLARE @RELEASE_FLAG	BIT
	DECLARE @WAFER_IDS_TABLE 		TABLE ( WAFER_ID INT )
	DECLARE @LOT_NUMBER VARCHAR(50)

	SET @WIPCURSOR = CURSOR FOR SELECT LOT_ID, PROCESSSTEP_ID, RELEASE_FLAG, LOT_NUMBER FROM #RESULTS
	
	OPEN @WIPCURSOR
	FETCH NEXT FROM @WIPCURSOR INTO @LOT_ID, @DB_PROCESSSTEP_ID, @RELEASE_FLAG, @LOT_NUMBER
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		--SELECT DISTINCT @ASSY_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @DB_PROCESSSTEP_ID,1 ) WHERE PS_TYPE= 'ASSEMBLY'

		--SELECT @LOT_SIZE = ISNULL(Parameter_value, 0) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @ASSY_PROCESSSTEP_ID AND Parameter_KEY = 'Lot Size'
		IF @RELEASE_FLAG = 0
		BEGIN		
				INSERT INTO @WAFERS_TABLE (WAFER_ID, QTY_IN) SELECT WAFER_ID, BIN_1_BALANCE_QTY FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_BALANCE_QTY > 0 ORDER BY CAST(WAFER_ID AS INT)

				SELECT @WAFER_IDS = COALESCE( @WAFER_IDS + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2), WAFER_ID ) )
					FROM @WAFERS_TABLE ORDER BY WAFER_ID 

				SELECT @QTY_AS_STRING = COALESCE( @QTY_AS_STRING + ';' + CONVERT( VARCHAR(10), QTY_IN ), CONVERT( VARCHAR(10), QTY_IN ) )
					 FROM @WAFERS_TABLE ORDER BY WAFER_ID
				
				UPDATE #RESULTS SET WAFER_IDS = @WAFER_IDS, BIN_QTY_STRING = @QTY_AS_STRING, NEXT_VENDOR = @NEXT_VENDOR_NAME WHERE LOT_ID = @LOT_ID	AND LOT_NUMBER = @LOT_NUMBER
		END
		ELSE
		BEGIN
				INSERT INTO @WAFER_IDS_TABLE SELECT WAFER_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_Lot_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> 'CANCELLED' AND LOT_ID = @LOT_ID AND LOT_TRANSFERRED = 1 AND SHIPPED_LOT_NUMBER = @LOT_NUMBER
				SELECT @WAFER_IDS = COALESCE( @WAFER_IDS + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2), WAFER_ID ) )
					FROM @WAFER_IDS_TABLE ORDER BY WAFER_ID
				SELECT @NEXT_VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = (SELECT TOP 1 TO_VENDOR_ID FROM ASSM_RELEASE_TABLE ART,ASSM_RELEASE_Lot_TABLE ARLT WHERE ART.assembly_release_ID = ARLT.assembly_release_ID AND STATUS <> 'CANCELLED' AND Lot_ID = @LOT_ID AND SHIPPED_LOT_NUMBER = @LOT_NUMBER )

				UPDATE #RESULTS SET WAFER_IDS = @WAFER_IDS, BIN_QTY_STRING = @QTY_AS_STRING, NEXT_VENDOR = @NEXT_VENDOR_NAME WHERE LOT_ID = @LOT_ID	AND LOT_NUMBER = @LOT_NUMBER
		END

		DELETE FROM @WAFERS_TABLE
		DELETE FROM @WAFER_IDS_TABLE
		SET @LOT_ID = NULL
		SET @LOT_SIZE = NULL
		SET @DB_PROCESSSTEP_ID = NULL
		SET @ASSY_PROCESSSTEP_ID = NULL
		SET @QTY_AS_STRING = NULL
		SET @WAFER_IDS = NULL
		SET @NEXT_VENDOR_NAME = NULL
		SET @LOT_NUMBER		= NULL
		FETCH NEXT FROM @WIPCURSOR INTO @LOT_ID, @DB_PROCESSSTEP_ID, @RELEASE_FLAG,@LOT_NUMBER
		
	END
	CLOSE @WIPCURSOR
	DEALLOCATE @WIPCURSOR
	
	IF @IS_SHOW_RELEASE_LOT = 'FALSE'
	BEGIN
		SELECT * FROM #RESULTS WHERE RELEASE_FLAG = 0 ORDER BY PART_NUMBER DESC 
	END
	ELSE
	BEGIN
		SELECT * FROM #RESULTS ORDER BY PART_NUMBER DESC 
	END
END
